{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########调包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import *\n",
    "import time\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "############数据文件文件路径\n",
    "train_dir = '../../contest/train/'\n",
    "B_dir = '../../contest/B榜/'\n",
    "train_pickle_dir = './pickle/train/'\n",
    "B_pickle_dir = './pickle/B/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_kurt(series_x):\n",
    "    kurt = series_x.kurt()\n",
    "    return kurt\n",
    "def get_mode(series_x):\n",
    "    mode = (series_x.mode())[0]\n",
    "    return mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工企业存款():\n",
    "    res = []\n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            企业存款_T0 = pd.read_csv(os.path.join(data_dir,'XW_DP_CUST_SUM.csv'))  \n",
    "        else:\n",
    "            企业存款_T0 = pd.read_csv(os.path.join(data_dir,'XW_DP_CUST_SUM_B.csv'))\n",
    "        企业存款_T0.columns = ['数据日期','客户ID','账户数','人民币金额']\n",
    "\t\t\n",
    "        企业存款_T0['人民币金额'] = pow((企业存款_T0['人民币金额'])/3.12,3).round(2)\n",
    "\n",
    "        企业存款_T0['数据日期'] = 企业存款_T0['数据日期'].astype('str')\n",
    "        企业存款_T0['数据日期'] = 企业存款_T0['数据日期'].astype('datetime64[ns]')\n",
    "        企业存款_T0['数据日期']=pd.to_datetime(企业存款_T0['数据日期'])+pd.DateOffset(days=11886)\n",
    "        企业存款_T0.sort_values(['客户ID','数据日期'],inplace=True,ascending=True)\n",
    "\t\t\n",
    "        企业存款_T1=企业存款_T0.groupby(['客户ID','数据日期']).agg({'人民币金额':['sum'],'账户数':['max']})\n",
    "        企业存款_T1.reset_index(inplace=True)\n",
    "        企业存款_T1.columns = ['客户ID','数据日期','人民币金额','账户数']\n",
    "\n",
    "        企业存款_T1['存款月差']=企业存款_T1.groupby(['客户ID'])['人民币金额'].diff(1)\n",
    "        企业存款_T1['存款季差']=企业存款_T1.groupby(['客户ID'])['人民币金额'].diff(3)\n",
    "\n",
    "        企业存款_T2=企业存款_T1.groupby(['客户ID']).agg({'人民币金额':['last','mean','std'],'账户数':['last','max','min']\\\n",
    "\t\t,'存款月差':['last','mean','skew',get_kurt,'std','sum','max','min'],'存款季差':['last']})\n",
    "        企业存款_T2.reset_index(inplace=True)\n",
    "        企业存款_T2.columns = ['客户ID','企业最新存款','企业存款_mean','企业存款_std','企业存款账户数','企业存款账户数_max'\\\n",
    "\t\t,'企业存款账户数_min','企业存款月差_last','企业存款月差_mean','企业存款月差_skew','存款月差_kurt'\\\n",
    "\t\t,'存款月差_std','存款年增值','存款月差_max','存款月差_min','存款季差']\n",
    "\t\t\n",
    "        企业存款_T2['企业存款_标准偏差'] = (100*企业存款_T2['企业存款_std']/企业存款_T2['企业存款_mean']).round(2)\n",
    "        #企业存款_T2['企业存款_标准偏差'] = 企业存款_T2['企业存款_标准偏差'].replace([np.inf,-np.inf],np.nan)\n",
    "\t\t\n",
    "        企业存款_T3= 企业存款_T1.loc[企业存款_T1['数据日期'] =='2020-12-31']\n",
    "        企业存款_T3.drop(['数据日期','账户数'],axis=1,inplace=True)\n",
    "        企业存款_T3.columns = ['客户ID','企业年底存款余额','企业年底存款月差','企业年底存款季差']\n",
    "\n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        企业存款特征=目标客户列表.merge(企业存款_T2,on=['客户ID'],how='left')\n",
    "        企业存款特征=企业存款特征.merge(企业存款_T3,on=['客户ID'],how='left')\n",
    "        企业存款特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "\n",
    "        pickle.dump(企业存款特征, open(pickle_dir+'企业存款特征.p', 'wb'))\n",
    "        res.append(企业存款特征)\n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-4-716532110a63>:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  企业存款_T3.drop(['数据日期','账户数'],axis=1,inplace=True)\n",
      "<ipython-input-4-716532110a63>:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  企业存款_T3.drop(['数据日期','账户数'],axis=1,inplace=True)\n"
     ]
    }
   ],
   "source": [
    "企业存款_训练集,企业存款_测试集=加工企业存款()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50000, 20)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "企业存款_训练集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5939, 20)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "企业存款_测试集.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 加工存款特征补充2():\n",
    "    res = [] \n",
    "    for data_dir,pickle_dir in [(train_dir,train_pickle_dir),(B_dir,B_pickle_dir)]:\n",
    "        if data_dir==train_dir:\n",
    "            存款表 = pd.read_csv(os.path.join(data_dir,'XW_DP_CUST_SUM.csv'))    \n",
    "        else:\n",
    "            存款表 = pd.read_csv(os.path.join(data_dir,'XW_DP_CUST_SUM_B.csv'))    \n",
    "         \n",
    "        存款表.columns =  ['数据日期','客户ID','账户数','人民币金额']\n",
    "\n",
    "        存款表.sort_values(['客户ID','数据日期'],inplace=True,ascending=True)\n",
    "        存款表.drop_duplicates(subset=None,keep='first',inplace=True)#整行去重\n",
    "\n",
    "\n",
    "        存款表['人民币金额'] = pow((存款表['人民币金额'])/3.12,3).round(2)\n",
    "        存款表['数据日期']= 存款表['数据日期'].astype('str')\n",
    "        存款表['数据日期'] = 存款表['数据日期'].astype('datetime64[ns]')\n",
    "        存款表['数据日期']=pd.to_datetime(存款表['数据日期'])+pd.DateOffset(days=11886)\n",
    "        \n",
    "        存款表_0=存款表.groupby(['客户ID']).agg({'人民币金额':['sum','max','mean','std','last']})\n",
    "        存款表_0.reset_index(inplace=True)\n",
    "        存款表_0.columns = ['客户ID','人民币金额汇总','人民币金额最大','人民币金额平均','人民币金额标准差','人民币金额最新']\n",
    "        \n",
    "        ##时间差\n",
    "        存款表00=存款表.groupby(['客户ID']).agg({'数据日期':['max']})\n",
    "        存款表00.reset_index(inplace=True)\n",
    "        存款表00.columns = ['客户ID','数据日期最新']\n",
    "        存款表00=存款表00.merge(存款表,on=['客户ID'],how='left')\n",
    "        存款表00['时间差'] = pd.to_datetime(存款表00['数据日期最新'])-pd.to_datetime(存款表00['数据日期'])\n",
    "        存款表00['时间差'] = 存款表00['时间差'].astype('timedelta64[D]')\n",
    "       \n",
    "        \n",
    "        #近6个月数据\n",
    "        存款表0=存款表00[存款表00['时间差']<=180]\n",
    "        存款表1=存款表0.groupby(['客户ID']).agg({'人民币金额':['mean',get_mode],'账户数':['mean']}) \n",
    "        存款表1.reset_index(inplace=True)\n",
    "        存款表1.columns = ['客户ID','人民币金额平均','人民币金额汇总','账户数'] \n",
    "        存款表1['企业近6月账户平均']=存款表1['人民币金额平均']/存款表1['账户数']\n",
    "        存款表1['企业近6月存款日均']=存款表1['人民币金额平均']/30\n",
    "        存款表1.drop(['人民币金额平均','人民币金额汇总','账户数'],axis=1,inplace=True)  \n",
    "\n",
    "        存款表3=存款表00[存款表00['时间差']<=90]\n",
    "        存款表3=存款表3.groupby(['客户ID']).agg({'人民币金额':['mean',get_mode],'账户数':['mean']}) \n",
    "        存款表3.reset_index(inplace=True)\n",
    "        存款表3.columns = ['客户ID','人民币金额平均','人民币金额汇总','账户数'] \n",
    "        存款表3['企业近3月账户平均']=存款表3['人民币金额平均']/存款表3['账户数']\n",
    "        存款表3['企业近3月存款日均']=存款表3['人民币金额平均']/30\n",
    "       \n",
    "        存款表3['企业近3月账户平均同比']=(存款表1['企业近6月存款日均']-存款表3['企业近3月存款日均'])/存款表3['企业近3月账户平均']\n",
    "        存款表3.drop(['人民币金额汇总','人民币金额平均','账户数','企业近3月存款日均'],axis=1,inplace=True) \n",
    "\n",
    "            \n",
    "        存款表2=存款表.groupby(['客户ID']).agg({'账户数':['mean','sum'],'人民币金额':['mean']})\n",
    "        存款表2.reset_index(inplace=True)\n",
    "        存款表2.columns = ['客户ID','平均账户数','账户数','人民币金额汇总']\n",
    "        存款表2['账户平均余额']=存款表2['人民币金额汇总']/存款表2['平均账户数']\n",
    "        存款表2.drop(['人民币金额汇总','平均账户数'],axis=1,inplace=True)   \n",
    "                       \n",
    "        if data_dir==train_dir:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(train_dir,'XW_TARGET.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID','违约标记']\n",
    "            目标客户列表.drop(['违约标记'],axis=1,inplace=True)\n",
    "        else:\n",
    "            目标客户列表 = pd.read_csv(os.path.join(data_dir,'XW_TARGET_B.csv'))\n",
    "            目标客户列表.columns = ['借款合同编号','客户ID','纳税人识别号','法定代表人客户ID']\n",
    "\n",
    "        存款特征=目标客户列表.merge(存款表_0,on=['客户ID'],how='left')\n",
    "        存款特征=存款特征.merge(存款表2,on=['客户ID'],how='left')\n",
    "        存款特征=存款特征.merge(存款表1,on=['客户ID'],how='left')\n",
    "        存款特征=存款特征.merge(存款表3,on=['客户ID'],how='left')\n",
    "\n",
    "        存款特征.drop(['借款合同编号','纳税人识别号','法定代表人客户ID'],axis=1,inplace=True)\n",
    "        pickle.dump(存款特征, open(pickle_dir+'Z存款特征.p', 'wb'))\n",
    "\n",
    "        res.append(存款特征)\n",
    "        \n",
    "    return res[0],res[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "存款特征_训练集,存款特征_训练集= 加工存款特征补充2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
